﻿Imports System.Data.OleDb
Imports System.Text
Imports System.Data.SqlClient
Imports ExcelApp = Microsoft.Office.Interop.Excel
Imports BUS
Imports DTO
Imports DAO
Public Class ExpostToExell



    Public Shared Sub Export(ByVal filepath As String)


        Try
            Dim dt As DataTable = HoaDonBUS.layTatCaTB()
            Dim EApp As New ExcelApp.Application
            Dim EBook As ExcelApp.Workbook

            Dim ESheet As ExcelApp.Worksheet
            Dim oValue As Object = System.Reflection.Missing.Value

            EBook = EApp.Workbooks.Add(oValue)

            ESheet = EBook.Worksheets("Sheet1")

            


            'Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "C1");
            'head.MergeCells = true;
            'head.Value2 = title;
            'head.Font.Bold = true;
            'head.Font.Name = "Tahoma";
            'head.Font.Size = "18";
            'head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            EApp.Rows.ColumnWidth = 20
            Dim xlRow As Long = 2
            Dim xlCol As Short = 1
            For Each col As DataColumn In dt.Columns
                ESheet.Cells(1, xlCol) = col.ColumnName
                xlCol += 1
            Next


            For Each row As DataRow In dt.Rows
                With ESheet
                    xlCol = 1
                    For Each col As DataColumn In dt.Columns
                        .Cells(xlRow, xlCol) = row(xlCol - 1)
                        xlCol += 1
                    Next
                End With
                xlRow += 1
            Next

            ESheet.SaveAs(filepath)
            EBook.Close()
            EApp.Quit()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub Import(ByVal file As String)
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0 Xml;"
        Dim dt As New DataTable

        Try
            Dim con As New OleDbConnection(connString)
            con.Open()
            Dim cmd As New OleDbCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = ("select * from [Sheet1$]")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            da.SelectCommand = cmd
            da.Fill(dt)
            con.Close()

            Dim cn As New DataProvider()
            If dt.Rows.Count <> 0 And dt.Columns.Count = 20 Then
                For Each dr As DataRow In dt.Rows
                    Dim MaLoai As LoaiDTO = LoaiBUS.LayTheoID(dr.Item("MaLoai"))
                    If MaLoai.TenLoai = String.Empty Then
                        MessageBox.Show("Mã Loại không có Trong Danh Muc " + dr.Item("MaLoai").ToString, "Lỗi")
                        Return
                    End If
                Next

                For Each dr As DataRow In dt.Rows
                    Dim param As SqlParameter() = New SqlParameter(4) {}
                    param(0) = New SqlParameter("@TenSP", dr.Item("TenSP"))
                    param(1) = New SqlParameter("@NgaySanXuat", dr.Item("NgaySanXuat"))
                    param(2) = New SqlParameter("@GiaBan", dr.Item("GiaBan"))
                    cn.ExecuteNonQuery("InserSP", param)
                Next
            End If
        Catch ex As Exception
            MessageBox.Show("Lỗi Impost File!", "Lỗi")
        End Try

    End Sub
End Class
